import os ,re

coderegex = re.compile(r'\d{6,}')


def filtercode_infile(file):
	codelist = []
	fp = open(file)
	for line in fp :
		m = coderegex.search(line)
		if m :
			code = m.group(0)
			codelist.append(code)
	return codelist 

if __name__=="__main__":
	files=[ file for file in os.listdir(os.getcwd()) if file.endswith('txt') ]
	codeset = set()
	for f in files :
		codelist = filtercode_infile(f)
		codeset.update(codelist)
	
	codetemplate = ""
	for c in codeset :
		codetemplate += "'%s'," % c	
	codes = codetemplate[0:-1]
	
	date = input("date:")
	timedomain =input("timeDomain:")
	dbname = 'hdu_sunrun_3'
	
	sqltemplate = """
		insert into %s.achievement 
			(comment,date,isValid,mileage,speed,timeDomain,runner_id) 
		select
			'手工导入','%s' ,1,1610,1.71,'%s' , id
		from
			%s.runner 
		where
		    code in (%s)			
		"""
	sql = sqltemplate % ( dbname , date , timedomain , dbname , codes )
	sqlfile = open("codesql.sql" , 'w' ,encoding='utf-8')
	sqlfile.write(sql)
	sqlfile.close()